# coding: utf-8

import datetime
import json
from decimal import Decimal

import pandas as pd
from flask import request, views
from flask_jwt_extended import jwt_required

from exts import db, log, rdb
from server.apps.account.models import AccBasic
from server.apps.account.views import AccTradeView
from server.apps.fund.forms import FundTradeForm
from server.apps.fund.models import FundBasic, FundHold, FundTrade
from server.apps.fund.spider import Spider
from server.services.xirr import xirr
from server.utils.IDCreator import IDCreator
from server.utils.restful import rest_success, rest_error


class FundHoldView(views.MethodView):
    rdb_key = "fund_trade"  # 用于基金交易产生流水号

    @classmethod
    def create_id(cls):
        return IDCreator.create_id(key=cls.rdb_key, year_related=True)

    @staticmethod
    @jwt_required()
    def get():
        acc_id = request.args.get("acc_id")
        sql = """select hold.acc_id, hold.fund, hold.name, hold.begin_date, hold.hold_num, hold.hold_cost,
                 fb.worth_date, fb.unit_worth from fund_hold hold
                 left join fund_basic fb on hold.fund = fb.fund
                 where hold.acc_id = :acc_id"""
        funds = db.session.execute(sql, {"acc_id": acc_id}).fetchall()

        funds = [dict(zip(x.keys(), x)) for x in funds]
        df = pd.DataFrame(funds)
        if len(funds):
            df["mkt_value"] = df["hold_num"] * df["unit_worth"]
            df[["y_rate", "bias", "advise"]] = df.apply(  # 计算年化收益率，回撤率,卖出建议
                lambda x: FundHoldView.compute_attributes(x), axis=1, result_type="expand")
            df["begin_date"] = df["begin_date"].apply(lambda x: x.strftime("%Y-%m-%d"))
            df["worth_date"] = df["worth_date"].apply(lambda x: x.strftime("%Y-%m-%d"))
            df["float_profit"] = df["mkt_value"] - df["hold_cost"]
            df["float_p_rate"] = (df["mkt_value"] - df["hold_cost"]) / df["hold_cost"] * 100
            df["ratio"] = df["mkt_value"] / df["mkt_value"].sum() * 100

        acc_info = dict()
        if df.empty:
            acc_info["mkt_value"] = 0
            acc_info["float_profit"] = 0
            acc_info["float_p_rate"] = 0
        else:
            acc_info["mkt_value"] = round(df["mkt_value"].sum(), 2)
            acc_info["float_profit"] = round(df["float_profit"].sum(), 2)
            acc_info["float_p_rate"] = round((df["mkt_value"].sum() - df["hold_cost"].sum()) /
                                             df["hold_cost"].sum() * 100, 2)

        balance = AccBasic.query.with_entities(AccBasic.balance).filter(AccBasic.acc_id == acc_id).one()
        acc_info["balance"] = balance[0]
        acc_info["total"] = acc_info["mkt_value"] + acc_info["balance"]
        acc_info["y_rate"] = FundHoldView.__xirr_acc(acc_id=acc_id, amount=acc_info["total"])

        sql = """select sum( case dc when 'D' then amount when 'C' then -amount end) cost from acc_trade
                    where acc_id = :acc_id and type_ in ('转入', '转出')
                """
        cost = db.session.execute(sql, params={"acc_id": acc_id}).fetchone()[0]  # 转入账户净值
        cost = 0 if cost is None else cost
        acc_info["all_profit"] = acc_info["total"] - cost
        return rest_success(message="获取数据成功", data={"acc_info": acc_info, "funds": df.to_dict(orient='records')})

    @staticmethod
    def compute_attributes(row):
        """
        计算基金各种属性
        :param row:
        :return: y_rate 年化收益率
                 down_rate 回撤率
                 advise 操作建议
        """
        y_rate = FundHoldView.__xirr(acc_id=row["acc_id"], fund=row["fund"], begin_date=row["begin_date"],
                                     worth_date=row["worth_date"], mkt_value=row["mkt_value"])
        bias = FundHoldView.get_bias(row["fund"], row["worth_date"])  # 乖离率
        # 计算赎回比例
        sale_ratio = FundHoldView.need_sale(fund=row["fund"], worth_date=row["worth_date"])
        advise = ""
        if y_rate > 10 and sale_ratio > 0:
            sale_num = int(row["hold_num"] * sale_ratio * (Decimal(1) + bias / Decimal(100.00)))
            sale_num = 10 if sale_num < 10 else sale_num  # 赎回数量小于10份则赎回10份
            sale_num = sale_num if row["hold_num"] - sale_num > 10 else row["hold_num"]  # 剩余数量小于10份则一起赎回
            advise = "卖出" + str(sale_num) + "份"
        return [y_rate, bias, advise]

    @staticmethod
    def need_sale(fund, worth_date):
        """
        判断是否有下跌波动大
        :param fund: 基金代码
        :param worth_date: 净值日期
        :return: true: 需要赎回; false: 不需要赎回
        """
        sale_ratio = 0
        date = worth_date + datetime.timedelta(days=-365 * 2)
        sql = """select * from fund_worth where fund = :fund and worth_date >= :date"""
        results = db.session.execute(sql, params={"fund": fund, "date": date}).fetchall()
        results = [dict(zip(x.keys(), x)) for x in results]
        df = pd.DataFrame(results)
        # 布林线
        df["avg"] = df["unit_worth"].rolling(20).mean()  # 平均值
        df["std"] = df["unit_worth"].rolling(20).std()
        df["up"] = df["avg"] + 2 * df["std"]
        df["down"] = df["avg"] - 2 * df["std"]
        df["unit_worth"] = df["unit_worth"].astype("float")

        df["ma10"] = df["unit_worth"].rolling(10).mean()
        df["ma10_std"] = df["ma10"].rolling(10).std()

        # 建议
        # 1.上升过快； 2.趋势：最后两个交易日10日均线超过0.5倍标准差且净值下降
        last_row = df.iloc[-1]
        last_2row = df.iloc[-2]
        if last_row["unit_worth"] > last_row["up"] \
                or (last_row["ma10"] - last_2row["ma10"] < - last_row["ma10_std"] * 0.5
                    and last_row["unit_worth"] < last_2row["unit_worth"]):
            sale_ratio += 0.08
        return Decimal(sale_ratio)

    @staticmethod
    def __xirr_acc(acc_id, amount):
        """
        获取账户年化收益率
        :param acc_id: 账户代码
        :param amount: 余额和市值合计
        :return:
        """
        sql = """select post_date date, 
                case dc
                    when 'D' then amount
                    when 'C' then -amount
                end as money
                from acc_trade
                where type_ in ('转入', '转出')
                  and acc_id = :acc_id"""
        results = db.session.execute(sql, {"acc_id": acc_id})
        results = [dict(zip(x.keys(), x)) for x in results]
        df = pd.DataFrame(results)
        df = df.append(pd.DataFrame({"date": datetime.date.today(), "money": -amount}, index=[0]), ignore_index=True)
        return round(xirr(df) * 100, 2)

    @staticmethod
    def __xirr(acc_id, fund, begin_date, worth_date, mkt_value):
        """
         计算年化收益率
        :param fund: 基金代码
        :param begin_date: 起始日期
        :param worth_date: 净值日期
        :param mkt_value:  市值
        :return:
        """
        sql = """select post_date date, 
                case operation 
                    when '申购' then amount
                    when '赎回' then -amount
                    when '现金红利' then -amount
                end as money
                from fund_trade
                where acc_id = :acc_id and fund = :fund
                  and post_date >= :begin_date"""
        results = db.session.execute(sql, {"acc_id": acc_id, "fund": fund, "begin_date": begin_date})
        results = [dict(zip(x.keys(), x)) for x in results]
        df = pd.DataFrame(results)
        df = df.append(pd.DataFrame({"date": worth_date, "money": -mkt_value}, index=[0]), ignore_index=True)
        guess = 0
        while True:
            try:
                ratio = xirr(df, guess) * 100
                break
            except Exception as e:
                log.logger.error(str(e))
                if df["money"].sum() > 0:
                    guess -= 0.11
                else:
                    guess += 0.11
                if guess > 1 or guess < -1:
                    break
                ratio = 0
        return ratio

    @staticmethod
    def get_bias(fund: str, worth_date):
        """
         计算年化收益率
        :param fund: 基金代码
        :param worth_date: 净值日期
        :return: 返回年化
        """
        date = worth_date + datetime.timedelta(days=-90)
        sql = """select worth_date, unit_worth from fund_worth where fund = :fund and worth_date >= :date"""
        df = db.session.execute(sql, {"fund": fund, "date": date}).fetchall()
        df = [dict(zip(x.keys(), x)) for x in df]
        df = pd.DataFrame(df)
        if df.shape[0] < 30:
            return 0
        df["ma30"] = df["unit_worth"].rolling(30).mean()
        row = df.iloc[-1]
        return (row["unit_worth"] - Decimal(row["ma30"])) / Decimal(row["ma30"]) * 100

    @classmethod
    def post(cls):  # 基金申购、赎回、现金红利、分红再投资
        data = request.get_data().decode("utf-8")
        data = json.loads(data)
        form = FundTradeForm(**data)
        try:
            form.validate()
            if data["number"] == 0 and data["amount"] == 0:
                raise Exception("份额或金额有误")
        except Exception as e:
            log.logger.error(str(e))
            return rest_error(str(e))
        data = form.data

        lock_trade = rdb.lock(name=data["acc_id"] + data["fund"], blocking_timeout=5)

        try:
            error = None
            locked = lock_trade.acquire()
            if not locked:
                raise Exception("加锁失败")
            operation = data["operation"]
            if operation == "申购":
                cls.buy_fund(data)
            elif operation == "赎回":
                cls.sale_fund(data)
            elif operation == "现金红利":
                cls.cash_fund(data)
            elif operation == "分红再投资":
                cls.number_fund(data)
            db.session.commit()
        except Exception as e:
            error = e
            db.session.rollback()
            log.logger.error(str(e))

        if locked:
            lock_trade.release()
        if error:
            return rest_error(str(error))
        return rest_success("基金交易成功")

    @classmethod
    def buy_fund(cls, params: dict):
        """
        基金申购
        :return:
        """
        fund_trade_id = cls.create_id()
        acc_id = params["acc_id"]
        fund = params["fund"]

        # 更新持仓数据
        fund_hold = FundHold.query.filter(FundHold.acc_id == acc_id, FundHold.fund == fund).first()
        if fund_hold:  # 基金已经持有
            fund_hold.hold_cost += Decimal.from_float(params["amount"]).quantize(Decimal("0.00"))
            fund_hold.hold_num += Decimal.from_float(params["number"]).quantize(Decimal("0.00"))
        else:
            fund_hold = FundHold(acc_id=acc_id, fund=fund, name=params["name"], begin_date=params["post_date"],
                                 hold_cost=params["amount"], hold_num=params["number"])
            db.session.add(fund_hold)

        # 添加交易记录
        fund_trade = FundTrade(trade_id=fund_trade_id, acc_id=acc_id, fund=fund, post_date=params["post_date"],
                               operation=params["operation"], amount=params["amount"], number=params["number"])
        db.session.add(fund_trade)

        # 更新账户余额
        dict_out = {"acc_id": params["acc_id"], "post_date": params["post_date"],
                    "type_": "投资", "fund_trade_id": fund_trade_id,
                    "amount": params["amount"]}
        AccTradeView.decrease_amount(params=dict_out)

    @classmethod
    def sale_fund(cls, params: dict):
        """
        基金赎回
        :return:
        """
        fund_trade_id = cls.create_id()
        acc_id = params["acc_id"]
        fund = params["fund"]
        number = Decimal.from_float(params["number"]).quantize(Decimal("0.00"))
        amount = Decimal.from_float(params["amount"]).quantize(Decimal("0.00"))

        # 更新持仓数据
        fund_hold = FundHold.query.filter(FundHold.acc_id == acc_id, FundHold.fund == fund).first()
        if not fund_hold:  # 基金未持有
            raise Exception("未持有基金{fund}，不能赎回".format(fund=fund))
        if fund_hold.hold_num < number:
            raise Exception("基金{fund}份额不足，不能赎回".format(fund=fund))
        cost = (fund_hold.hold_cost * number / fund_hold.hold_num).quantize(Decimal("0.00"))  # 赎回成本
        fund_hold.hold_cost -= cost
        fund_hold.hold_num -= number

        # 添加交易记录
        profit = amount - cost
        fund_trade = FundTrade(trade_id=fund_trade_id, acc_id=acc_id, fund=fund, post_date=params["post_date"],
                               operation=params["operation"], amount=params["amount"], number=params["number"],
                               profit=profit)
        db.session.add(fund_trade)

        # 更新账户余额
        dict_in = {"acc_id": params["acc_id"], "post_date": params["post_date"], "type_": "回收",
                   "fund_trade_id": fund_trade_id, "amount": params["amount"]}
        AccTradeView.increase_amount(params=dict_in)

    @classmethod
    def cash_fund(cls, params: dict):
        """
        现金分红，持仓成本降低
        :return:
        """
        fund_trade_id = cls.create_id()
        acc_id = params["acc_id"]
        fund = params["fund"]
        amount = Decimal.from_float(params["amount"]).quantize(Decimal("0.00"))

        # 更新持仓数据
        fund_hold = FundHold.query.filter(FundHold.acc_id == acc_id, FundHold.fund == fund).first()
        if not fund_hold:  # 基金未持有
            raise Exception("未持有基金{fund}，不能操作".format(fund=fund))
        fund_hold.hold_cost -= amount

        # 添加交易记录
        fund_trade = FundTrade(trade_id=fund_trade_id, acc_id=acc_id, fund=fund, post_date=params["post_date"],
                               operation=params["operation"], amount=params["amount"], number=0)
        db.session.add(fund_trade)

        # 更新账户余额
        dict_in = {"acc_id": params["acc_id"], "post_date": params["post_date"], "type_": "回收",
                   "fund_trade_id": fund_trade_id, "amount": params["amount"]}
        AccTradeView.increase_amount(params=dict_in)

    @classmethod
    def number_fund(cls, params: dict):
        """
        红利再投资
        :return:
        """
        fund_trade_id = cls.create_id()
        acc_id = params["acc_id"]
        fund = params["fund"]

        # 更新持仓数据
        fund_hold = FundHold.query.filter(FundHold.acc_id == acc_id, FundHold.fund == fund).first()
        if not fund_hold:  # 基金未持有
            raise Exception("未持有基金{fund}，不能操作".format(fund=fund))
        fund_hold.hold_num += Decimal.from_float(params["number"]).quantize(Decimal("0.00"))

        # 添加交易记录
        fund_trade = FundTrade(trade_id=fund_trade_id, acc_id=acc_id, fund=fund, post_date=params["post_date"],
                               operation=params["operation"], amount=0, number=params["number"])
        db.session.add(fund_trade)


class FundBasicView(views.MethodView):
    @staticmethod
    def get():
        # data = request.get_data().decode("utf-8")
        fund = request.args.get("fund")
        if fund != "":  # 传入具体基金代码，就从天天基金网获取基本信息
            # data = json.loads(data)
            # fund = data.get("fund")
            try:
                fund_info = Spider.scrapy_fund(fund)
            except Exception as e:
                return rest_error(str(e))
            return rest_success("天天基金网基本信息", fund_info)

        res = FundBasic.query.all()
        if res:
            return rest_success("找到数据", [x.dict_ for x in res])
        else:
            return rest_error("未找到数据")

    @staticmethod
    def post():  # 基金操作
        data = request.get_data().decode("utf-8")
        data = json.loads(data)
        fund_info = FundBasic(**data["form"])
        try:
            db.session.add(fund_info)
            db.session.commit()
            return rest_success("基金{fund}添加成功".format(fund=fund_info.fund))
        except Exception as e:
            db.session.rollback()
            message = "基金{fund}添加失败 {message}".format(fund=fund_info.fund, message=str(e))
            log.logger.error(message)
            return rest_error(message)

    @staticmethod
    def delete():
        data = request.get_data().decode("utf-8")
        data = json.loads(data)
        fund = data.get("fund")
        if not fund:
            return rest_error("请输入基金代码")
        fund_obj = FundBasic.query.filter(FundBasic.fund == fund).first()
        try:
            db.session.delete(fund_obj)
            db.session.commit()
            return rest_success("删除基金{fund}成功".format(fund=fund))
        except Exception as e:
            db.session.rollback()
            log.logger.error(str(e))
            return rest_error("删除基金{fund}失败".format(fund=fund))


class ReportView(views.MethodView):
    @classmethod
    def get(cls):
        sql = """
            select fh.acc_id, ab.name acc_name, fh.fund, fh.name fund_name, fh.hold_num, fb.unit_worth, fb.ttype, 
            fb.worth_date from fund_hold fh 
            left join fund_basic fb on fb.fund = fh.fund
            left join acc_basic ab on fh.acc_id = ab.acc_id
        """
        results = db.session.execute(sql).fetchall()
        results = [dict(zip(x.keys(), x)) for x in results]
        df = pd.DataFrame(results)
        del results
        df["mkt_value"] = df["hold_num"] * df["unit_worth"]
        df["l_worth"] = df.apply(lambda x: cls.get_last_worth(x["fund"], x["worth_date"]), axis=1)
        df["diff"] = (df["unit_worth"] - df["l_worth"]) * df["hold_num"]
        df["ratio"] = df["mkt_value"] / df["mkt_value"].sum() * 100
        df["worth_date"] = df["worth_date"].apply(lambda x: x.strftime("%Y-%m-%d"))
        total = round(df["diff"].sum(), 2)

        group_ttype = df.groupby(by=["ttype"])["mkt_value"].sum()
        df_ttype = pd.DataFrame(group_ttype)
        df_ttype.reset_index(inplace=True)
        df_ttype["ratio"] = df_ttype["mkt_value"] / df_ttype["mkt_value"].sum() * 100
        return rest_success("报表获取数据成功", data={
            "ttype": df_ttype.to_dict(orient="records"),
            "fund": {
                "funds": df.to_dict(orient="records"),
                "profit": total
            }
        })

    @classmethod
    def get_last_worth(cls, fund: str, worth_date: datetime.date):
        sql = """
                select unit_worth from fund_worth 
                where fund = :fund and worth_date = 
                (select max(worth_date) from fund_worth where fund = :fund and worth_date < :worth_date)
            """
        last_worth = db.session.execute(sql, params={"fund": fund, "worth_date": worth_date}).fetchone()
        if not last_worth:
            return 0
        return last_worth[0]
